SQL server 2008 R2安装及操作数据库的命令

一、安装SQL server 2008 R2

我这次安装SQL server 2008 R2 是基于window server 2008，所以我们首先要在虚拟机上安装window server 2008系统。注意安装SQL server 2008 R2之前需要安装Microsoft .net framework 3.5组件。在产品密钥这个选项，我安装的这个版本自带密钥的安装，产品密钥是GYF3T-H2V88-GRPPH-HWRJP-QRTYB。安装引导界面几乎都是默认下一步，不过在功能选择这一项的时候需要选中数据库引擎服务和管理工具。注意：一台设备可以安装多个数据库引擎服务，但是只能安装一次管理工具。

在实例配置这个选项可以选择默认实例或者自己命名实例。注意：每新建一个实例都相当于重新在本地新建一个服务，我们可以在每个实例下都新建多个数据库，每个数据库下也可以新建多个数据表。当我们选择默认实例后，完成后本地服务上(运行窗口键入services.msc查看当前设备的所有服务信息)就会有一个名为sql server(MSSQLSERVER)的服务启动；当我们新建一个test的实例时，完成后本地服务上就会多出一个名为sql server(TEST)的服务。

在服务器配置选项中的服务账户下的代理和database engine中选择这一项NT AUTHORITY\NETWORK SERVICE，然后下一步。

在数据库引擎配置页面中的账户设置下，需要设置数据库的身份验证模式、sa用户的密码和sql server 管理员。之后一直默认下一步，直到完成安装。

二、数据库的相关命令

2.1、关系模型

关系：一个关系对应通常说的一张表，如上图的这张学生信息表；
元组：表中的一行即为一个元组；
属性：表中的一列即为一个属性，属性的名即为属性名；
码：也称为码键，可以唯一确定一个元组，如上图中的学号就是本关系的码；
域：域是一组具有相同数据类型的值的集合，属性的取值范围来自某个域，如人的年龄在1~120之间；
分量：元组中的一个属性值。
关系模型中有三种完整性约束：实体完整性、参照完整性和用户定义的完整性。简而言之，实体完整性就是要保证主键不能为空，且主属性的每个值唯一；参照完整性就是当两个关系存在属性的引用，其中一个关系的非主属性引用了另一个关系的主属性，那么这个关系的非主属性的值就得参照另一个关系的主属性的值取值；用户定义的完整性就是用户自己设定的约束。

2.2、创建数据库、数据表

--创建数据库
Create database LibraryDB
--创建数据表
Create table student
(
	SNO int primary key,
	Sname varchar(15),
	Sage char(2),
	Smobile char(11),
	Saddress varchar(50),
	Semail varchar(100),
)
Create table student
(
	SNO int,
	Sname varchar(15),
	Sage char(2),
	Smobile char(11),
	Saddress varchar(50),
	Semail varchar(100),
	constraint PK_SNO primary key clustered(SNO),
)
--创建复合主键
Create table student
(
	SNO int,
	Sname varchar(15),
	Sage char(2),
	Smobile char(11),
	Saddress varchar(50),
	Semail varchar(100),
	constraint PK_SNO primary key clustered(SNO,Sname),
)

在数据表创建的时候，可以给列增加约束性条件。

--创建数据表，增加约束性条件
Create table student
(
	SNO int,
	Sname varchar(15) not null,
	Sage char(2),
	Smobile char(11),
	Saddress varchar(50),
	Semail varchar(100),
	constraint PK_SNO primary key clustered(SNO),
	constraint UN_MB unique NONClustered(Smobile),
	constraint CH_MB check(mobile like '[1][3-9][0-9]{10}'),
	constraint CH_EMAIL check(Semail like '_%[@]_%[.]_%')
)

在数据表创建的时候，可以给列设定自动编号和默认值。

--创建数据表，设定列自动编号和默认值
Create table student
(
	SNO int identity(95001,1),
	Sage char(2) default '男',
	constraint PK_SNO primary key clustered(SNO),
)

在数据表创建的时候，指定外键。

--创建数据表，设定列自动编号和默认值
Create table BorrowBook
(
	SNO int,
	BookId int
	Sage char(2),
	constraint PK_SNO primary key clustered(SNO,BookId),
	constraint FK_SNO foreign key(SNO) references student(SNO),
	constraint FK_BookId foreign key(BookId) references book(BookId),
)

2.3、增删改

--假设现在有个表student
--插入一条数据
insert into student(SNO,Sname,Sage...) values(95001,'张三'，13...)
--改变一条记录
update student set Sname = '李四' where SNO = 95001
--删除一条数据
delete from student where SNO=95001

2.4、查询数据

现在有以下六张表，分别是学生表student、图书表book、图书种类表booktype、作者信息表Author、借书表borrowbook、书籍出版信息表press

--- 基础查询 ---

--1、查询出姓名不是“陈鹏”的学生的所有信息
Select SNO, SName, Sage, Sex, MobileNO, StuEMail from Student Where SName !='陈鹏' 
--或者 where SName <> '陈鹏'
--或者 Where SName Not In('陈鹏')

--2、查询出学生年龄介于20到30间的学生学号和姓名
Select SNO,SName from Student Where Sage Between 20 and 30 
--或者 where Sage >= 20 And Sage <=30 

--3、查询哪些学生没有填写“年龄”信息
Select SNO,SName from Student Where Sage is Null 

--4、查询一共有多少学生借了书 Distinct 【消除重复项】
Select Distinct SNo from BorrowBook

--- Top 使用 ---

--5、查询学生表的前三行
Select Top 3 SNO, SName, Sage, Sex, MobileNO, StuEMail from Student

--6、查询学生表的前30%行
Select Top 30 percent SNO, SName, Sage, Sex, MobileNO, StuEMail from Student

--- like及通配符 使用 ---

--7、查询出手机号码134或者135开头，倒数第四位为不是5也不是6的学生姓名（Like）
Select SName As '学生姓名' from student where MobileNO Like '[1][3][45]%[^56]___'

--- order by、group by及排序使用 ---

--8、查询学生表随机返回多少行
Select Top 3 SNO, SName, Sage, Sex, MobileNO, StuEMail from Student order by NEWID()

--9、统计出男女生的人数 
Select Sex As '性别',count(*) As '人数' from Student Group by Sex

--10、对Student表按照年龄升序排序，如果年龄一样，女生排在男生前面 (order by)
-- ASC【升序】  DESC【降序】 排序也可以嵌套
Select SNO, SName, Sage, Sex, MobileNO, StuEMail from student order by Sage ASC , Sex DESC

--- 聚合函数的使用SUM/AVG/MIN/MAX/COUNT ---
--11、查询出男生的平均年龄
Select avg(Sage) As '男生的平均年龄' from student where Sex = '男'

--12、查询出有多少位学生借书
Select Count(Distinct SNO) As '借书的学生数量' from BorrowBook

--- 嵌套查询、多表查询---

--13、查询出借过书的同学的姓名 (distinct)
Select SName As '借过书的学生姓名' from student Where SNO in 
(
	Select Distinct SNO from BorrowBook
)

--14、查询出年龄最大的学生的学号和姓名  (max)
Select SNo As '学号',SName AS '姓名' from Student where Sage = 
(	
	Select Max(Sage) from Student
)

--15、查询出计算机类的图书总共有多少本
Select Sum(BookSumNo) As '计算机类的图书数量' from Book where BookTypeID =
(
	Select BookTypeID From BookType Where TypeName = '计算机'
)

--16、统计出每一类书中的最高的价格 使用两张表：Book,BookType 
Select T2.TypeName AS '类别名称',T1.maxPrice As '最高价格' from 
(
	Select BookTypeId,MAX(BookPrice) As 'maxPrice' from Book Group by BookTypeID
) As T1, BookType As T2 Where T1.BookTypeID = T2.ID
 
--17、查询出借的最多的书的名称
Select BookName AS '借的最多的图书名称' from Book where BookId In
(
    --获得借的最多的书的图书编号
	Select  BookId from BorrowBook Group by BookId
	Having count(*) =
	(   -- 获得借的最多的书的数量
		Select  Top 1 count(*) from BorrowBook Group by BookId order by count(*) DESC
	)
)

三、sql server新建用户

3.1、sql server新建用户

Sql server中有一个超级管理员SA，这个管理员拥有最高权限。当然我们可以新建新的用户，并给予新用户相应的权限。具体的操作步骤如下图：
(1)、首先点击安全性-登录名，然后右击选择新建登录名；

(2)、然后注意下图红框中的填写就可以了；

(3)、之后就可以通过新建的登录名连接数据库了；注意这里的服务器名称的填写方式，对于本地的sql server来说，我们可以写”.”、”localhost”、”127.0.0.1”以及本机的ip地址。

3.2、给新建用户赋数据库权限

新建完用户，用户默认对任何数据库和数据表都没有操作权限，我们需要自己手动添加。打开登录名-属性，打开用户映射。我们可以指定用户可以对哪些数据库有哪些权限，如下图：

这里十个数据库角色成员身份代表的含义是：

数据库角色成员身份名	说明
db_accessadmin	可以为windows登录名、Windows组和SQL server登录名添加或删除数据库访问权限
db_backupoperator	可以备份数据库
db_datareader	可以从所有数据表中增删改数据
db_datawriter	可以从所有数据表中查询数据
db_ddladmin	可以在数据库中运行任何数据定义语言(DDL)
db_denydatareader	不能增删改数据库下的所有数据表的数据
db_denydatawriter	不能查询数据库下的所有数据表的数据
db_owner	最大权限，可以执行所有关于数据库以及数据表的操作，包括删除数据库
db_securityadmin	可以修改角色成员身份和管理权限
public	默认，什么权限都不分配

3.3、给新建用户赋数据表权限

上面我们只是对数据库整体赋权限，权限对数据库下的所有数据表同时生效，但是如果我们想要对某个具体的数据库下的数据表赋权限，则需要打开数据库下面的数据表，右击打开属性：

在跳出的窗口选择权限，然后点击搜索，输入用户名，然后就可以指定这个用户对这个数据表的操作权限了。

四、数据库的备份与还原

4.1、图形化界面操作

如下图，选择备份类型的时候有三种类型：完整备份即是对所有数据库都全备份一次；差异备份只备份自上一次完整备份后增加的内容；事务日志备份是对上一次全备份+事务日志备份之后增加的内容备份。备份的路径默认是C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Backup。当我们要还原的时候，就右击数据库-任务-还原-文件和文件组，然后选择对应的备份集点击确定就可以了。

4.2、命令行

--为了以后的方便，我们可以先指定备份的设备（备份设备在服务器对象-备份设备里可以找到），备份设备绑定了物理地址。
Exec sp_addumpdevice 'disk','备份设备名','备份文件存放的绝对路径'

--- 完整备份 ---
backup database [数据库名] to [备份设备名] with init
--- 完整备份还原 ---
--recovery表示此次还原是最后一次还原,如果是norecovery则表示接下来还有还原操作
restore database [数据库名] from [备份设备名] with file=1,recovery

--- 差异备份 ---
backup database [数据库名] to [备份设备名] with differential,noinit
--- 差异备份还原 ---
--先还原最近的一次完整备份数据
restore database [数据库名] from [备份设备名] with file=1,norecovery
--再还原最近一次的差异备份数据
restore database [数据库名] from [备份设备名] with file=2,recovery

--- 事务日志备份 ----
backup log [数据库名] to [备份设备名]
--- 事务日志备份还原 ---
--先还原最近的一次完整备份数据
restore database [数据库名] from [备份设备名] with file=1,norecovery
--再还原最近一次的差异备份数据,stopat表示把数据还原到这一时间点前
restore database [数据库名] from [备份设备名] with file=2,stopat='2018-09-06 11:00:00',recovery

最后更新： 2018年09月15日 15:42

原始链接： http://www.yeguangjie.xyz/2018/09/15/4-SQL server 2008 R2安装及操作数据库的命令-2018-09-09/

YeGuangJie's Blog

分享我的知识与见解，希望可以帮到你